﻿CREATE PROCEDURE state_Move
	@ID int,
	@Direction int, -- -1 back, 1 forward, 0 to nearest by Dir
	@StatusFromID int,
	@StatusToID int
AS
SET NOCOUNT ON

DECLARE @SQL nvarchar(max),@TableName sysname
SELECT @TableName = S.TableName,
			 @SQL = 'UPDATE '+S.TableName+' WITH(UPDLOCK) SET StatusID = dbo.fs_NextState(StatusID,@Direction) WHERE ID = @ID'
FROM t_State S WHERE S.ID = @StatusToID

EXEC sp_executeSql @SQL,N'@Direction int,@ID int',@Direction,@ID

DECLARE @StatusID int
SET @SQL = N'SELECT @StatusID = StatusID FROM '+@TableName+' WHERE ID = @ID'

EXEC sp_executeSql @SQL,N'@ID int,@StatusID int OUT',@ID,@StatusID OUT

RETURN @StatusID

--BEGIN TRAN
--DECLARE @Ret int
--EXEC @Ret = state_Move 716,1,NULL,316
--PRINT ISNULL(@Ret,0)
--ROLLBACK

